package jnpf.database.sql.impl;

import jnpf.database.enums.DbAliasEnum;
import jnpf.database.enums.DbSttEnum;
import jnpf.database.model.DbFieldMod;
import jnpf.database.source.DbBase;
import jnpf.database.sql.SqlBase;
import jnpf.database.sql.append.insert.InsertSql;
import jnpf.database.sql.append.insert.InsertSqlDTO;

import java.util.List;

/**
 * MySQL SQL语句模板
 *
 * @author JNPF开发平台组
 * @version V3.2.0
 * @copyright 引迈信息技术有限公司
 * @date 2021/10/6
 */
public class SqlOracle extends SqlBase {

    @Override
    protected void setSqlBase() {
        //TODO BINARY_FLOAT类型查询不出来，这个语句有隐患
        fieldListSql =
                "SELECT * FROM \n" +
                        "\n" +
                        "(\n" +
                        "SELECT DISTINCT\n" +
                        "\tA.column_name AS " + DbAliasEnum.FIELD_NAME.AS() + ",\n" +
                        "\tA.data_type AS " + DbAliasEnum.DATA_TYPE.AS() + ",\n" +
                        "\tA.CHAR_COL_DECL_LENGTH AS " + DbAliasEnum.DATA_LENGTH.AS() + ",\n" +
                        "CASE\n" +
                        "\t\tA.nullable \n" +
                        "\t\tWHEN 'N' THEN\n" +
                        "\t\t'0' ELSE '1' \n" +
                        "\tEND AS " + DbAliasEnum.ALLOW_NULL.AS() + ",\n" +
                        "CASE\n" +
                        "\tA.nullable \n" +
                        "\tWHEN 'N' THEN\n" +
                        "\t'1' ELSE '0' \n" +
                        "\tEND AS " + DbAliasEnum.PRIMARY_KEY.AS() + ",\n" +
                        "\tB.comments AS " + DbAliasEnum.FIELD_COMMENT.AS() +
                        "\nFROM\n" +
                        "\tuser_tab_columns A,\n" +
                        "\tuser_col_comments B,\n" +
                        "\tall_cons_columns C,\n" +
                        "\tUSER_TAB_COMMENTS D \n" +
                        "WHERE\n" +
                        "\ta.COLUMN_NAME = b.column_name \n" +
                        "\tAND A.Table_Name = B.Table_Name \n" +
                        "\tAND A.Table_Name = D.Table_Name \n" +
                        "\tAND ( A.TABLE_NAME = c.table_name ) \n" +
                        "\tAND A.Table_Name = '" + DbSttEnum.TABLE.getTarget() + "'\n" +
                        ") A,\n" +
                        "(\n" +
                        "select a.column_name name,case when a.column_name=t.column_name then 1 else 0 end " + DbAliasEnum.PRIMARY_KEY.AS() + "\n" +
                        "from user_tab_columns a\n" +
                        "left join (select b.table_name,b.column_name from user_cons_columns b\n" +
                        "join user_constraints c on c.CONSTRAINT_NAME=b.CONSTRAINT_NAME\n" +
                        "where c.constraint_type   ='P') t\n" +
                        "on a.table_name=t.table_name\n" +
                        "where a.table_name='" + DbSttEnum.TABLE.getTarget() + "'\n" +
                        ") B WHERE A." + DbAliasEnum.FIELD_NAME.AS() + " = b.NAME";
        tableListSql =
                "SELECT " +
                        "a.TABLE_NAME " + DbAliasEnum.TABLE_NAME.AS() + ", " +
                        "b.COMMENTS " + DbAliasEnum.TABLE_COMMENT.AS() + ", " +
                        "a.num_rows " + DbAliasEnum.TABLE_SUM.AS() +
                        "\nFROM user_tables a, user_tab_comments b "
                        + "WHERE a.TABLE_NAME = b.TABLE_NAME "
        /*+ "and a.TABLESPACE_NAME='"+ DbSttEnum.TABLE_SPACE.getTarget()+"'"*/
        ;
    }

    public SqlOracle(DbBase dbBase) {
        super(dbBase);
    }

    @Override
    public String getDeleteSql(String table) {
        return "DROP TABLE " + table + ";";
    }

    @Override
    public String batchInsertSql(List<List<DbFieldMod>> dataList, String table) {
        InsertSqlDTO iInsertSqlDTO = new InsertSqlDTO(this.dbBase, table, dataList, ";");
        return InsertSql.batch(iInsertSqlDTO);
    }

    @Override
    public String[] getPageSql(String sql, String sortType, Integer currentPage, Integer pageSize) {
        /*mysql可以用 SELECT SQL_CALC_FOUND_ROWS * FROM table
                LIMIT index;SELECT FOUND_ROWS();方法获得两个结果集*/
        // 获取dataListSql
        String dataListSql = "SELECT *FROM ("
                + "SELECT jnpf_tt.*, ROWNUM AS rowno FROM (" + sql + ") jnpf_tt ORDER BY " + sortType
                + ") jnfp_tab WHERE jnfp_tab.rowno between " + (currentPage - 1) * pageSize
                + " and " + currentPage * pageSize;
        // 获取totalSql
        String totalSql = "SELECT COUNT(*) totalRecord FROM (" + sql + ") jnpf_tab";
        return new String[]{dataListSql, totalSql};
    }

    /*==================特有的一些方法======================*/

    /**
     * oracle 时间格式转换
     *
     * @param dataType
     * @param value
     * @return
     */
    public static String getOracleDataTime(String dataType, String value) {
        if ("date".equals(dataType.toLowerCase()) || dataType.toLowerCase().contains("time")) {
            value = "TO_DATE('" + value + "','YYYY-MM-DD HH24:MI:SS')";
        } else {
            value = "'" + value + "'";
        }
        return value;
    }

    /**
     * 添加修改sql
     *
     * @param sql
     * @return
     */
    public String jdbcCreUpSql(String sql) {
        String jdbcSql = "";
        //添加数据Sql处理
        if (sql.toLowerCase().contains("insert") && sql.replaceAll(" ", "").contains("),(")) {
            String[] splitSql = sql.split("\\),\\(");
            //centerSql取出INTO TEST_DETAILS ( F_ID, F_RECEIVABLEID)
            String centerSql = splitSql[0].split("VALUES")[0].split("INSERT")[1];
            //for循环尾部
            String lastSql = splitSql[splitSql.length - 1];
            splitSql[splitSql.length - 1] = lastSql.substring(0, lastSql.length() - 1);
            for (int i = 0; i < splitSql.length; i++) {
                //第一个语句INSERT INTO TEST_DETAILS ( F_ID, F_RECEIVABLEID) VALUES ( '71', '28bf3436e5d1'
                //需要拼接成 INSERT INTO TEST_DETAILS ( F_ID, F_RECEIVABLEID) VALUES ( '71', '28bf3436e5d1'）
                String sqlFlagm;
                if (i == 0) {
                    sqlFlagm = splitSql[i] + ")";
                } else {
                    sqlFlagm = "INSERT " + centerSql + "VALUES (" + splitSql[i] + ")";
                }
                jdbcSql = jdbcSql + sqlFlagm;
            }
        } else {
            jdbcSql = sql;
        }
        return jdbcSql;
    }


}
